package main

import (
	"database/sql"
	"fmt"
	"github.com/gin-gonic/gin"
	_ "github.com/go-sql-driver/mysql"
	"log"
	"strconv"
	"time"
)

func insertHandler(c *gin.Context) {
	group_name := c.Query("group_name")
	branch := c.Query("branch")
	create_time := time.Now().Format("2006.01.02 15:04:05")
	groups := Groups{0, group_name, branch, create_time}

	insert(dbs2, groups)

	fmt.Println(groups)
	c.JSON(200, groups)
}

func delectHandler(c *gin.Context) {
	id, _ := strconv.Atoi(c.Query("id"))
	delete(dbs2, id)
	fmt.Println(id)
	c.JSON(200, id)
}

func updateHandler(c *gin.Context) {
	id, _ := strconv.Atoi(c.Query("id"))
	group_name := c.Query("group_name")
	branch := c.Query("branch")
	create_time := time.Now().Format("2006.01.02 15:04:05")
	groups := Groups{id, group_name, branch, create_time}

	update(dbs2, groups)

	fmt.Println(groups)
	c.JSON(200, groups)
}

func queryHandler(c *gin.Context) {
	id, _ := strconv.Atoi(c.Query("id"))
	groups := query(dbs2, id)
	c.JSON(200, groups)
}

func queryRowsHandler(c *gin.Context) {
	id, _ := strconv.Atoi(c.Query("id"))
	groups := queryRows(dbs2, id)
	c.JSON(200, groups)
}

func main() {

	r := gin.Default()

	dbInfo := "root:nopass.2@tcp(10.160.84.134:3306)/HttpRunnerPlatform"
	initDB2(dbInfo)

	// http://127.0.0.1:8080/insert?group_name=benjamin&branch=123
	r.GET("/insert", insertHandler)

	// http://127.0.0.1:8080/delete?id=4
	r.GET("/delete", delectHandler)

	// http://127.0.0.1:8080/update?group_name=mlic&branch=321&id=6
	r.GET("/update", updateHandler)

	// http://127.0.0.1:8080/query?id=6
	r.GET("/query", queryHandler)

	// http://127.0.0.1:8080/query?id=6
	r.GET("/queryRows", queryRowsHandler)

	r.Run()

}

// 封装方法
var dbs2 *sql.DB // 数据库连接池
func initDB2(str string) (err error) {
	// dbs2, err := sql.Open("mysql", str) 此处不能写成 :=,否则不能和 var dbs2 赋值
	dbs2, err = sql.Open("mysql", str)
	if err != nil {
		fmt.Println(err)
		return err
	}

	//defer dbs2.Close()

	// 最大连接数
	dbs2.SetMaxOpenConns(30)
	// 最大空闲数
	dbs2.SetMaxIdleConns(2)

	errA := dbs2.Ping()
	if errA != nil {
		fmt.Println(errA)
		return errA
	}
	fmt.Println("连接成功...")
	return nil
}

type Groups struct {
	// 首字母大写可供后续直接json返回，可与数据库中大小写不一致
	Id          int
	Group_name  string
	Branch      string
	Create_time string
}

func insert(db *sql.DB, groups Groups) {
	db.Begin()
	stmt, err := db.Prepare("insert into groups(group_name,branch,create_time) values (?,?,?)")
	if err != nil {
		log.Panicln(err)
	}
	_, errA := stmt.Exec(groups.Group_name, groups.Branch, groups.Create_time)
	if errA != nil {
		log.Panicln(errA)
	}
	stmt.Close()
	//defer db.Close()
}

func delete(db *sql.DB, id int) {
	db.Begin()
	stmt, err := db.Prepare("delete from groups where id=?")
	if err != nil {
		log.Panicln(err)
	}
	_, errA := stmt.Exec(id)
	if errA != nil {
		log.Panicln(errA)
	}
	stmt.Close()
}

func update(db *sql.DB, groups Groups) {
	db.Begin()
	stmt, err := db.Prepare("update groups set group_name=?, branch=?, create_time=? where id=?")
	if err != nil {
		log.Panicln(err)
	}
	// 注意 stmt 传入参数的顺序需要保持一致
	_, errA := stmt.Exec(groups.Group_name, groups.Branch, groups.Create_time, groups.Id)
	if errA != nil {
		log.Panicln(errA)
	}
	stmt.Close()
}

func query(db *sql.DB, id int) (groups Groups) {
	var group_name string
	var branch string
	var create_time string
	// 不能写 *，因为 Scan后面已设定值。 .Scan 切片,要注意Scan的先后
	rowObj := db.QueryRow("select group_name,branch,create_time from groups where id=?", id).Scan(&group_name, &branch, &create_time)
	if rowObj != nil {
		log.Panicln(rowObj)
	} else {
		fmt.Printf("查询到gropus: group_name%v,branch%v,&%v", group_name, branch, create_time)
	}
	groups = Groups{id, group_name, branch, create_time}
	return groups
}

func queryRows(db *sql.DB, id int) []Groups {
	rowsObj, err := db.Query("select id,group_name,branch,create_time from groups where id>?", id)
	if err != nil {
		log.Panicln(err)
	}

	// 注意关闭
	defer rowsObj.Close()

	var groups []Groups
	for rowsObj.Next() {
		var group Groups
		err := rowsObj.Scan(&group.Id, &group.Group_name, &group.Branch, &group.Create_time)
		if err != nil {
			log.Panicln(err)
		} else {
			fmt.Println(group)
			groups = append(groups, group)
		}

	}

	return groups
}
